import os
import pickle

import numpy as np
import pandas as pd
from tensorflow import keras

from src.tablet.model import tablet_model_config
from src.tablet.predict.tablet_price_config import MAX_EVN_PRICE
from src.tablet.preprocessor.model_data_preprocessor import preprocess_model_data
from src.utils.config import logger
from src.utils.db_processor import presto_processor
from src.utils.util import get_today

PRODUCT_SQL = """
SELECT distinct
    dps.mapping_product_sku_id as product_sku_id,
    dp.product_id,
    CASE WHEN otpmp.propertyleveltemplateid IS NULL THEN -1 ELSE otpmp.propertyleveltemplateid END AS product_level_template_id,
    otpm.secondary_level_template_id AS secondary_level_template_id
FROM dim.dim_product dp 
JOIN dim.dim_product_sku_channel_mapping dps ON dp.product_id = dps.product_id and dps.business_channel_id=1 
JOIN ods.ods_trunk_producttemplatemapping otpmp ON (dp.product_id = otpmp.productid AND otpmp.templatetype = 1 and otpmp.business_channel = '0')
JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpm ON dp.product_id = otpm.product_id and otpm.business_channel=1
WHERE dp.product_category_id = 6 and otpm.business_channel=1 and dps.business_channel_id=1
"""

COMPLETE_DATA_SQL = """
SELECT
    cast(dps.product_sku_id as int) as product_sku_id,
    'product_id' AS property,
    cast(dp.product_id as VARCHAR) AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
WHERE dp.product_category_id = 6  and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=6
)

UNION

SELECT
     cast(dps.product_sku_id as int) as product_sku_id,
    'product_level_template_id' AS property,
    cast(otpm.secondary_level_template_id as VARCHAR) AS property_value
FROM dim.dim_product dp 
JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
JOIN ods.ods_opt_foundation_secondary_product_template_mapping otpm ON dp.product_id = otpm.product_id and otpm.business_channel=1

WHERE dp.product_category_id = 6 and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=6
)

UNION 

SELECT
    cast(dps.product_sku_id as int) as product_sku_id,
    'product_sku_name' AS property,
    dps.product_sku_name AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
WHERE dp.product_category_id = 6 and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=6
)

UNION

SELECT
     cast(dps.product_sku_id as int) as product_sku_id,
    'product_name' AS property,
    dp.product_name AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
WHERE dp.product_category_id = 6 and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=6
)

UNION

SELECT
     cast(dps.product_sku_id as int) as product_sku_id,
    'product_brand_name' AS property,
    dp.product_brand_name AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
WHERE dp.product_category_id = 6 and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=6
)

UNION

SELECT
     cast(dps.product_sku_id as int) as product_sku_id,
    (CASE
    WHEN pssp.property_name = '内存' THEN 'memory'
    WHEN pssp.property_name = '储存容量' THEN 'storage'
    WHEN pssp.property_name = '苹果保修期时长' THEN 'guarantee'
    WHEN pssp.property_name = '网络模式' THEN 'networks'
    WHEN pssp.property_name = '购买渠道' THEN 'purchase_way'
    WHEN pssp.property_name = '颜色' THEN 'color'
    ELSE 'unknown_prop' END) AS property,
    pssp.property_value_name AS property_value
FROM dim.dim_product dp JOIN dim.dim_product_sku dps ON dp.product_id = dps.product_id
    JOIN dim.dim_product_sku_sub_product pssp ON pssp.product_sku_id = dps.product_sku_id
    AND pssp.property_name IN ('购买渠道', '储存容量', '网络模式', '内存', '苹果保修期时长', '颜色')
WHERE dp.product_category_id = 6 and dps.product_sku_id in (
select distinct mapping_product_sku_id
FROM  dim.dim_product_sku_channel_mapping dps   where dps.business_channel_id=1 and product_category_parent_id=6
)
"""

LEVEL_SQL = """
SELECT 
product_level_id as level_id,
product_level_name
FROM dim.dim_product_level
WHERE product_category_id = 6 AND is_product_level_active_flag = 1  and business_channel='0' 
"""

# LEVEL_TEMPLATE_MAPPING_SQL = """
# SELECT
# level_template_id AS product_level_template_id,
# level_id
# FROM dim.dim_product_level_template_mapping
# """

LEVEL_TEMPLATE_MAPPING_SQL = """ 
select secondary_level_template_id as product_level_template_id,
product_level_id as level_id
from  ods.ods_opt_foundation_secondary_product_level_template_level
"""


BRAND_ID_NAME_SQL = """
select distinct dp.product_brand_id,
dp.product_brand_name
from dim.dim_product dp where dp.product_id_status_id = 2 and dp.active = 1 
"""


class TabletSKUComplement:
    def __init__(self, model_date=None):
        if model_date is None:
            self.model_date = get_today()
        else:
            self.model_date = model_date
        self.predict_data = None

        self.__load_models()

    def __load_models(self):
        """
        读取最新的模型与预处理器
        :return:
        """
        logger.info('loading laptop complement models...')
        model_file = os.path.join(tablet_model_config.MODEL_DIR, tablet_model_config.MODEL_FILE_NAME)
        self.model = keras.models.load_model(model_file)

        with open(os.path.join(tablet_model_config.MODEL_DIR, tablet_model_config.TABLET_OHE_NAME), 'rb') as f:
            self.ohe = pickle.load(f)

        with open(os.path.join(tablet_model_config.MODEL_DIR, tablet_model_config.TABLET_SCALER_NAME), 'rb') as f:
            self.scaler = pickle.load(f)

        logger.info('loading laptop complement models done')

    def process_complement_data(self):
        """
        处理补全数据
        :return:
        """
        logger.info('processing complement data...')
        data = self.__load_complement_data()

        data = data.drop_duplicates(['product_sku_id', 'property'])
        data['property'] = data['property'].map(lambda x: x.strip())
        data['property_value'] = data['property_value'].map(lambda x: x.strip())
        data = data.pivot_table(index='product_sku_id', columns='property', values='property_value',
                                aggfunc=lambda x: x).reset_index()
        data.fillna('unknown', inplace=True)

        levels = self.__load_product_levels()

        # 做笛卡尔积
        data['_tmp_key'] = 0
        levels['_tmp_key'] = 0

        data = pd.merge(data, levels, on='_tmp_key', how='outer').drop(columns='_tmp_key')

        data = preprocess_model_data(data, is_cal_period=False)
        data['period'] = '0'
        data['product_category_id'] = 6
        data['product_category_name'] = '平板电脑'
        self.predict_data = data
        self.predict_data['date'] = self.model_date.strftime('%Y-%m-%d')

    def predict_complement_data(self):
        """
        预测补全数据
        :return:
        """
        if self.predict_data is None:
            logger.critical('predict_data is None!')
            return

        logger.info('predicting complement data...')
        x_inputs = self.ohe.transform(self.predict_data[tablet_model_config.TABLET_FEATURES])

        predict_scaled = self.model.predict(x_inputs)
        self.predict_data['forecast_reference_price'] = np.round(
            self.scaler.inverse_transform(predict_scaled).flatten()).astype(int)
        self.predict_data['forecast_reference_price'] = self.predict_data['forecast_reference_price'].where(
            self.predict_data['forecast_reference_price'] >= 10, 10)

        # 对无属性信息的产品使用默认价格
        env_protect = self.predict_data['product_level_template_id'] == tablet_model_config.EVN_TEMPLATE_ID
        self.predict_data.loc[env_protect, 'forecast_reference_price'] = self.predict_data.loc[
            env_protect, 'forecast_reference_price'].where(
            self.predict_data.loc[env_protect, 'forecast_reference_price'] <= MAX_EVN_PRICE, MAX_EVN_PRICE)

    # def save_complement_data(self):
    #     """
    #     保存补全数据
    #     :return:
    #     """
    #     if self.predict_data is not None:
    #         logger.info('saving complement data...')
    #         template_level_mapping = self.__load_template_level_mapping()
    #         insert_data = pd.merge(self.predict_data, template_level_mapping,
    #                                on=['product_level_template_id', 'level_id'])
    #         insert_sql = """
    #         INSERT INTO dm.dm_prognostizieren_preisergebnisse(predict_date, product_sku_key, product_sku_name,
    #         product_level_key, product_level_name, product_key, product_name, product_category_id,
    #         product_category_name, product_brand_name, forecast_reference_price)
    #         VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    #         """
    #         postgre_processor.execute_sql(
    #             "SELECT md.add_partition('dm.dm_prognostizieren_preisergebnisse','{}','day')".format(
    #                 self.model_date.strftime('%Y%m%d')))
    #         postgre_processor.execute_insert_postgresql(insert_sql,
    #                                              insert_data[
    #                                                  ['date', 'product_sku_id', 'product_sku_name', 'level_id',
    #                                                   'product_level_name', 'product_id', 'product_name',
    #                                                   'product_category_id', 'product_category_name',
    #                                                   'product_brand_name', 'forecast_reference_price']
    #                                              ].to_records(index=False).tolist())
    #         logger.info('saving complement data done')

    def __load_complement_data(self):
        """
        读取测试数据
        :return:
        """
        return presto_processor.load_sql(COMPLETE_DATA_SQL)

    def __load_product_levels(self):
        """
        读取等级
        :return:
        """
        return presto_processor.load_sql(LEVEL_SQL)

    def __load_template_level_mapping(self):
        """
        读取等级模板和等级对应关系
        :return:
        """
        mapping = presto_processor.load_sql(LEVEL_TEMPLATE_MAPPING_SQL)
        mapping['product_level_template_id'] = mapping['product_level_template_id'].astype(str)
        mapping['level_id'] = mapping['level_id'].astype(int)

        return mapping
